﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using System.Linq;

namespace QG.FrontDesk.Core
{
    public class DapperDBHelper<T>
    {
        private static string connStr = ConfigHelper.GetAppSettingsValue("connString");

        /// <summary>
        /// 返回第一个对象 注意：如果返回没有数据，此方法会报错
        /// string sql = "select * from dbo.AdminInfo where Sex = @Sex";
        /// 参数化处理
        /// AdminInfo admin = new AdminInfo();
        /// admin.Sex =0;
        /// AdminInfo queryFrist = DapperHelper<AdminInfo>.QueryFirst(sql, admin);
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QueryFirst(string sql, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                return con.QueryFirst<T>(sql, param);
            }
        }


        /// <summary>
        /// 查询返回一个int 值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int Query(string sql, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                return Convert.ToInt32(con.ExecuteScalar(sql, param));
            }
        }

        /// <summary>
        /// 返回第一个对象，如果没有则返回空对象
        /// string sql = "select * from dbo.AdminInfo where Sex=@Sex";
        /// 参数化对象
        /// AdminInfo queryFirstParam = new AdminInfo();
        /// queryFirstParam.Sex = 0;
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QueryFirstOrDefault(string sql, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                //return con.Query<T>(sql, param).FirstOrDefault();
                return con.QueryFirstOrDefault<T>(sql, param);
            }
        }


        /// <summary>
        /// 返回一个对象的集合
        /// string sql = "select * from dbo.AdminInfo where Sex=@Sex";
        /// 参数化对象
        /// AdminInfo queryFirstParam = new AdminInfo();
        /// queryFirstParam.Sex = 0;
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static List<T> QueryList(string sql, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                return con.Query<T>(sql, param).ToList();
            }
        }


        /// <summary>
        /// 返回单个对象 注意:如果返回没有数据，此方法会报错，如果返回有多条数据，此方法也会报错
        /// string sql = "select * from dbo.AdminInfo where Sex=@Sex";
        /// 参数化对象
        /// AdminInfo queryFirstParam = new AdminInfo();
        /// queryFirstParam.Sex = 0;
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QuerySingle(string sql, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                return con.QuerySingle<T>(sql, param);
            }
        }


        /// <summary>
        /// 返回单个对象，如果没有则返回空对象
        /// string sql = "select * from dbo.AdminInfo where Sex=@Sex";
        /// 参数化对象
        /// AdminInfo queryFirstParam = new AdminInfo();
        /// queryFirstParam.Sex = 0;
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QuerySingleOrDefault(string sql, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                return con.QuerySingleOrDefault<T>(sql, param);
            }
        }


        /// <summary>
        /// 增删改
        /// /// string sql = @"INSERT INTO [dbo].[AdminInfo]
        ///               (
        ///               [LoginName]
        ///               ,[LoginPwd]
        ///               ,[Sex]
        ///               )
        ///         VALUES
        ///               ( @LoginName
        ///               , @LoginPwd
        ///               , @Sex
        ///              )";
        ///参数化对象
        ///返回的单个对象，如果没有返回空对象
        ///AdminInfo queryFirstParam = new AdminInfo();
        ///queryFirstParam.LoginName = "张三";
        ///queryFirstParam.LoginPwd = "123456";
        ///queryFirstParam.Sex = 0;
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int Execute(string sql, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                return con.Execute(sql, param);
            }
        }


        /// <summary>
        /// 批量增删改（针对单表的，多个sql用事务）
        /// string sql = @"delete from dbo.AdminInfo where AdminId=@AdminId";
        /// 返回的单个对象，如果没有返回空对象
        /// List<AdminInfo> queryFirstParam = new List<AdminInfo>();
        /// queryFirstParam.Add(new AdminInfo() { AdminId = 16 });
        /// queryFirstParam.Add(new AdminInfo() { AdminId = 17 });
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public static int CountExecute(string sql, List<T> list)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                con.Open();
                using (var tran = con.BeginTransaction())
                {
                    int result = con.Execute(sql, list, tran);
                    tran.Commit();
                    return result;
                }
            }
        }


        /// <summary>
        /// 事务操作
        /// string sql = @"delete from dbo.AdminInfo where AdminId=12";
        /// string sql1 = @"update dbo.AdminInfo set RealName='张三' where AdminId=1";
        /// string[] array = new string[2];
        /// array[0] = sql;
        /// array[1] = sql1;
        /// </summary>
        /// <param name="sqlList"></param>
        /// <returns></returns>
        public static int AffairExcute(string[] sqlList)
        {
            int result = 0;
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                con.Open();
                //开启事务
                using (var tran = con.BeginTransaction())
                {
                    try
                    {
                        foreach (var sql in sqlList)
                        {
                            result += con.Execute(sql, null, tran);
                        }
                        //提交
                        tran.Commit();
                    }
                    catch (Exception)
                    {
                        result = 0;
                        //回滚
                        tran.Rollback();
                    }
                }
            }
            return result;
        }


        /// <summary>
        /// 事务操作含参数化
        /// string sql = @"delete from dbo.AdminInfo where AdminId=@AdminId";
        /// AdminInfo admin1 = new AdminInfo() { AdminId = 4 };
        /// string sql1 = @"update dbo.AdminInfo set RealName=@RealName where AdminId=@AdminId";
        /// AdminInfo admin2 = new AdminInfo() { RealName = "王五", AdminId = 1 };
        /// Dictionary<string, object> pairs = new Dictionary<string, object>();
        /// pairs.Add(sql, admin1);
        /// </summary>
        /// <param name="dic"></param>
        /// <returns></returns>
        public static int DictExcute(Dictionary<string, object> dic)
        {
            int result = 0;
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                con.Open();
                //开启事务
                using (var tran = con.BeginTransaction())
                {
                    try
                    {
                        foreach (var sql in dic)
                        {
                            result += con.Execute(sql.Key, sql.Value, tran);
                        }
                        //提交
                        tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        result = 0;
                        //回滚
                        tran.Rollback();
                    }
                }
            }
            return result;
        }


        /// <summary>
        /// 执行存储过程
        /// string proc = "PageListProc";
        /// DynamicParameters parameters = new DynamicParameters();
        /// parameters.Add("@PageIndex", 1);
        /// parameters.Add("@PageSize", 10);
        /// parameters.Add("@TableName", "dbo.AdminInfo");
        /// parameters.Add("@ColumnName", "*");
        /// parameters.Add("@Where", "");
        /// parameters.Add("@OrderBy", "AdminId");
        /// parameters.Add("@Count","",System.Data.DbType.Int32,System.Data.ParameterDirection.Output);
        /// List<AdminInfo> admins = DapperHelper<AdminInfo>.ExecuteProc(proc, parameters);
        /// int count = parameters.Get<int>("@Count");
        /// </summary>
        /// <param name="proc"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static List<T> ExecuteProc(string proc, object param)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                return con.Query<T>(proc, param, null, true, null, CommandType.StoredProcedure).ToList();
            }
        }

        /// <summary>
        /// 返回dataset
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql)
        {
            DataSet ds = new DataSet();
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();                
                MySqlCommand sqlCommand = new MySqlCommand(sql, conn);
                MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(sqlCommand);
                //
                sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                sqlDataAdapter.Fill(ds);
                return ds;
            }
        }
    }
}
